from collections import defaultdict
import random

from mcp_server_graph.utils.neo4j_util import Neo4jDriver

if __name__ == '__main__':

    import pandas as pd

    # 读取手机机型
    df = pd.read_csv('/Users/lijia/PycharmProjects/user-insight/insight_agent/mcp_server_graph/src/mcp_server_graph'
                     '/mocker/手机机型.csv', encoding='utf-8')
    device_table = df.to_dict('records')
    print(device_table)

    neo4j_driver = Neo4jDriver()

    # reset data
    neo4j_driver.execute("""
        match (p1:Phone)-[r:PayFor|Share]->(p2:Phone) 
        detach delete r 
        remove p1.is_family_plan, 
        p1.is_primary_user, 
        p1.family_plan_name, 
        p2.is_family_plan, 
        p2.is_primary_user
    """)

    neo4j_driver.execute("""
        match (p:Phone)-[use:use_device]->(d:Device) detach delete use, d
    """)

    # 读取用户到家庭圈映射
    records, _, _ = neo4j_driver.execute("""
        match (u:User)-[:has]->(p:Phone) 
        with distinct u.user_id as user_id, p.mobile_number as mobile_number, 
        p.current_operator as current_operator, u.group_fam_id as fam_id 
        return *
    """)

    """
    处理家庭套餐数据生成逻辑，直接写回neo4j
    """
    # 1. 按家庭圈ID分组
    families = defaultdict(list)
    for record in records:
        families[record.data()['fam_id']].append(record.data())

    # 2. 随机选择1/3的家庭圈
    all_fam_ids = list(families.keys())
    sample_size = max(1, len(all_fam_ids) // 3)  # 确保至少选择1个家庭
    selected_fam_ids = random.sample(all_fam_ids, sample_size)

    # 3. 为选中的家庭圈设置家庭套餐信息
    result = []
    for fam_id, members in families.items():
        # 所有家庭每个成员设置基本设备
        for member in members:
            print(f"设置号码{member["mobile_number"]}设备")
            rand_device = random.choice(device_table)
            tac = rand_device["tac"]
            mobile_number = member["mobile_number"]
            imei = str(tac) + str(mobile_number)[-4:] + member["user_id"][-4:]
            cypher = (f"""
                match (p:Phone) where p.mobile_number = {member["mobile_number"]} 
                create (d:Device) 
                set d.imei = {imei} 
                set d.tac = {tac} 
                set d.brand = '{rand_device["brand"]}' 
                set d.model = '{rand_device["model"]}' 
                set d.release_year = {rand_device["release_year"]} 
                set d.comment = '{rand_device["comment"]}' 
                set d.group_fam_id = p.group_fam_id 
                create (p)-[use:use_device]->(d) 
            """)
            neo4j_driver.execute(cypher)




        # 检查是否被选中
        is_selected = fam_id in selected_fam_ids
        if not is_selected:
            continue

        # 找出家庭中的移动用户
        mobile_users = [m for m in members if m['current_operator'] == '移动']

        if len(mobile_users) < 2:
            continue

        # 随机选择一个主卡（如果有移动用户）
        primary_user = None
        if mobile_users:
            primary_user = random.choice(mobile_users)

        # 处理每个家庭成员
        for other_user in mobile_users:
            if primary_user["mobile_number"] != other_user["mobile_number"]:
                print(f"connecting prim{primary_user} to sub{other_user}")
                cypher = (f"""
                    match (p1:Phone) where p1.mobile_number = {primary_user["mobile_number"]} 
                    match (p2:Phone) where p2.mobile_number = {other_user["mobile_number"]} 
                    create (p1)-[payfor:PayFor]->(p2) 
                    create (p1)-[share:Share]->(p2) 
                    set payfor.description = '主卡为副卡代付' 
                    set share.description = '主卡共享流量给副卡' 
                    set p1.is_primary_user = True 
                    set p1.is_family_plan = True 
                    set p1.family_plan_name = '家庭共享套餐' 
                    set p2.is_primary_user = False 
                    set p2.is_family_plan = True 
                """)
                neo4j_driver.execute(cypher)

                # 每个主副卡关系60%概率使用同一个设备
                if random.random() <= 0.6:
                    cypher = f"""
                        match (p1:Phone)-[used1:use_device]->(d1:Device) where p1.mobile_number = {primary_user["mobile_number"]} 
                        match (p2:Phone)-[used2:use_device]->(d2:Device) where p2.mobile_number = {other_user["mobile_number"]}
                        // detach delete used2, d2 
                        create (p2)-[used:use_device]->(d1)
                    """
                    print(f"sharing device prim{primary_user} to sub{other_user}")
                    neo4j_driver.execute(cypher)


    """处理诈骗"""
    records, _, _  = neo4j_driver.execute("""
    //诈骗 返回 诈骗用户和手机号
    
    MATCH (u:User)-[has:has]->(p:Phone)
    WHERE  date(p.activation_date) >= date() - duration({months: 12*9})
    
    WITH u, has, p,
      count{(p)-[]-(:Phone)} AS callCnt,
      count{(p)-[]->(:Phone)} AS calltoCnt,
      count{(p)<-[]-(:Phone)} AS callfromCnt
    WHERE calltoCnt * 100 / callCnt > 70
    
    MATCH (p)-[short_forien_call:called]-(op:Phone)
    WHERE short_forien_call.call_duration_minutes < 10 
      AND short_forien_call.roaming_type = '国际'
    WITH u, has, p, 
      collect(short_forien_call) AS cs, 
      collect(op) AS ops, 
      callCnt,
      calltoCnt,
      callfromCnt,
      count(short_forien_call) AS shortForienCnt
    WHERE shortForienCnt * 100 / callCnt > 60
    
    // 返回用户及其子图
    RETURN u.user_id as user_id, p.mobile_number as mobile_number
    """)
    fraud_users = []
    for record in records:
        fraud_users.append(record.data())

    grouped_frauds = defaultdict(list)
    for fraud_user in fraud_users[:20]:
        grouped_frauds[fraud_user["mobile_number"]] = []

    for fraud_user in fraud_users[20:]:
        group_main_number = random.choice(fraud_users[:20])["mobile_number"]
        # grouped_frauds[group_main_number].append(fraud_user["mobile_number"])
        cypher = f"""
            match (p1:Phone)-[used1:use_device]->(d1:Device) where p1.mobile_number = {group_main_number} 
            match (p2:Phone)-[used2:use_device]->(d2:Device) where p2.mobile_number = {fraud_user["mobile_number"]}
            // detach delete used2, d2 
            create (p2)-[used:use_device]->(d1)
            set d1.high_risk = True
            set p2.high_risk = True
            set p1.high_risk = True
        """
        print(f"sharing fraud device prim{group_main_number} to sub{fraud_user["mobile_number"]}")
        neo4j_driver.execute(cypher)
